package com.apes.scm.rbac.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.masterdata.party.model.Party;
import com.apes.scm.masterdata.party.model.PartyRole;
import com.apes.scm.rbac.model.Post;
import com.apes.scm.rbac.model.User;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

public interface UserRepository extends CustomRepository<User, Integer> {

    @Query(value = "select C.* from aut_post_users a,aut_posts b,aut_users c where a.post_id = b.id and a.user_id = c.id and b.merchant_management = 1 and c.origin = :id", nativeQuery = true)
    User findManagementUser(@Param("id") String id);

    User findByOrigin(String userId);

    @Query("SELECT T FROM User T WHERE T.mobileNumber = :mobileNumber AND T.disable = FALSE")
    List<User> findAllByMobileNumber(@Param("mobileNumber") String mobileNumber);

    User findByMobileNumber(String mobileNumber);

    @Query("select T from User T where T.person.id = :partyRoleId")
    List<User> findByPartyRoleId(@Param("partyRoleId") String partyRoleId);

    @Query(value = "select T.* from aut_users T where T.person_id = :party",nativeQuery = true)
    List<User> findByPartyRole(@Param("party") PartyRole party);

    /***
     * 通过Party 查询对应的 User
     * @param party
     * @return
     */
    @Query("select T from User T where T.person.party = :party")
    List<User> findByParty(@Param("party") Party party);
//    //常用 查询方法
//    @Query("select name from aut_users where beautyType = :beautyType AND time BETWEEN :startDate AND :endDate")
//    public List<User> findReservations(@Param("startDate")Date startDate, @Param("endDate")Date endDate , @Param("beautyType")String beautyType);
//
//    /**
//     * 更新是否可用标记
//     * @param id
//     * @param usable
//     */
//    @Modifying
//    @Query("update aut_users set usable = :usable where id = :id")
//    public int updateUsableFlag(@Param("id")Long id, @Param("usable")Boolean usable);
//
//    /**
//     * 删除
//     * @param ids
//     */
    @Modifying
//    @Query(value="delete from aut_users where id in (:ids)",nativeQuery=true)
    @Query("delete from User where id in (:ids)")
    public int delete(@Param("ids")Long[] ids);

    @Query(value = "SELECT t.* FROM aut_users t WHERE t.id IN (SELECT t.user_id from aut_post_users t WHERE t.post_id =?1) AND t.disable = 0",nativeQuery = true)
    List<User> findByPost(String post);


    @Query("select T from User T where lastUpdatedStamp > :times or (origin > :id and lastUpdatedStamp = :times) order by lastUpdatedStamp, id asc ")
    List<User> findByLastUpdatedStampAndId(@Param("times") Date times, @Param("id") String id, Pageable pageable);

    @Query(value = "SELECT t.* FROM aut_users t WHERE t.access_id is null ",nativeQuery = true)
    List<User> findByAccessIdIsNull();

    @Query(value = "select id\n" +
            "  from (select row_.*, rownum rownum_\n" +
            "           from (select id from aut_users order by id asc) row_\n" +
            "          where rownum <= decode(0, :page, :size, (:page + 1) * :size))\n" +
            " where rownum_ > :page * :size", nativeQuery = true)
    List<Number> findAllUserToId(@Param("page") int page, @Param("size") int id);

    @Query(value = "SELECT * FROM aut_users t WHERE t.origin in (SELECT t.origin FROM hr_employee t WHERE t.work_branch_id in :branchIds)",nativeQuery = true)
    List<User> findByBranch(@Param("branchIds") List<String> branchIds);

    List<User> findAllByDisableIsFalseOrderByIdAsc(Pageable pageable);

    @Query(value = "SELECT * FROM aut_users t " +
            "WHERE exists (select 1 from aut_post_users a, aut_posts b, aut_local_permit c , aut_local_permit_item d " +
            "where a.USER_ID = t.id and a.post_id = b.id  and b.local_permit_id = c.id and c.id = d.local_permit_id and d.type = 'pt_supplier' " +
            "and d.org_id in :supplierIds)",nativeQuery = true)
    List<User> findAllBySupplier(@Param("supplierIds")List<String> supplierIds);

    @Query(value = "SELECT t.*, (select distinct d.org_id  from aut_post_users a, aut_posts b, aut_local_permit c , aut_local_permit_item d \n" +
            "            where a.USER_ID = t.id and a.post_id = b.id  and b.local_permit_id = c.id and c.id = d.local_permit_id and d.type = 'pt_supplier') org_id FROM aut_users t " +
            "WHERE exists (select 1 from aut_post_users a, aut_posts b, aut_local_permit c , aut_local_permit_item d " +
            "where a.USER_ID = t.id and a.post_id = b.id  and b.local_permit_id = c.id and c.id = d.local_permit_id and d.type = 'pt_supplier' " +
            "and (d.org_id in :supplierIds or d.org_id in :supplierId2))",nativeQuery = true)
    List<Map> findAllBySupplier(@Param("supplierIds")List<String> supplierIds, @Param("supplierId2")List<String> supplierId2);

    @Query(value = "SELECT t.id FROM aut_users t  " +
            "WHERE  t.person_id in :supplierIds",nativeQuery = true)
    List<BigDecimal> findAllByListSupplier(@Param("supplierIds")List<String> supplierIds);

    @Query(value = "SELECT t.id FROM aut_users t WHERE  t.person_id =:supplierId",nativeQuery = true)
    List<BigDecimal> findBySupplier(@Param("supplierId")String supplierId);

    User findByName(String name);
}
